﻿create or replace function Get_2350_Cure_Program(i_bomName in varchar2, i_bomRev in varchar2, i_pnName in varchar2) return integer IS
  Result integer;  
  v_packageCategory a_packagecategory.packagecategoryname%TYPE;
  v_vendorCode vendor.vendorcode%TYPE;
  v_DAFname productbase.productname%TYPE;
BEGIN
  SELECT pc.packagecategoryname INTO v_packageCategory
  FROM product pt
  INNER JOIN productbase ptb ON ptb.productbaseid=pt.productbaseid
  INNER JOIN a_packagecategory pc ON pc.packagecategoryid=pt.packagecategoryid
  WHERE ptb.revofrcdid = pt.productid
    AND ptb.productname = i_pnName;

  IF v_packageCategory = 'UBGA' OR v_packageCategory = 'ULGA' OR
     v_packageCategory = '104LGA' OR v_packageCategory = 'INAND2' OR 
     v_packageCategory = 'INAND3' OR v_packageCategory = 'INAND4' THEN
     SELECT ve.vendorcode INTO v_vendorCode
     FROM A_ProductBomMaterialList ml, Product prd, A_MaterialType mt,Vendor ve
     WHERE ml.productbomid = 
      ( SELECT p.productbomid FROM A_ProductBom p, A_ProductBomBase pb, product pd
        WHERE P.ProductBomBaseId = pb.ProductBomBaseId 
          AND pb.ProductBomName = i_bomName
          AND p.Revision = i_bomRev
          and pd.device = i_pnName)
     AND ml.MaterialPartId = prd.ProductId
     AND prd.MaterialTypeId = mt.MaterialTypeId
     and prd.vendorid=ve.vendorid
     AND mt.MaterialTypeName = 'DAF';
     IF v_vendorCode = 'HI' THEN
        RESULT := 10;
     ELSE
        RESULT := 8;
     END IF;
  ELSE
     IF i_bomName = '0G1162-07807-002G-WBC' THEN
       RESULT := 15;
     ELSE
       SELECT ptb.productname INTO v_DAFname
       FROM A_ProductBomMaterialList ml, Product prd, A_MaterialType mt, productbase ptb
       WHERE ml.productbomid = 
        ( SELECT p.productbomid FROM A_ProductBom p, A_ProductBomBase pb, product pd
          WHERE P.ProductBomBaseId = pb.ProductBomBaseId 
            AND pb.ProductBomName = i_bomName
            AND p.Revision = i_bomRev
            and pd.device = i_pnName)
       AND ml.MaterialPartId = prd.ProductId
       AND prd.MaterialTypeId = mt.MaterialTypeId
       AND ptb.productbaseid=prd.productbaseid
       AND mt.MaterialTypeName = 'DAF';
       IF v_DAFname='54-42-00116' THEN
          RESULT := 5;
       ELSIF v_DAFname='54-42-00115' THEN
          RESULT := 8;
       ELSE
          RESULT := 6;
       END IF;
     END IF;
  END IF;
  
  IF i_pnName IN ('54-62-05990-002G', -- This PN is hard code to meet Yi Qin's request @ 9/14/2010 by email
    '54-62-05991-002G','54-62-05993-002G','54-62-05994-002G','54-62-05995-002G' -- SQ00255618
    ) THEN
        RESULT := 8;    
  END IF;
  
  IF i_pnName IN ('54-82-08301-032G', -- This PN is hard code to meet Lu Peng's request
    '54-82-08505-032G' -- SQ00339856
    ) THEN
        RESULT := 5;    
  END IF;
  
  return(Result);
end Get_2350_Cure_Program;
/
